package com.project.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.project.bean.OrderBean;
import com.project.bean.UserBean;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface IOrderMapper extends BaseMapper<OrderBean> {
    /**
     * 根据商品类型、时间、用户类型查询订单的销售金额。
     * @param productType   产品类型
     * @param date  下单时间
     * @param userType  用户类型（名字）
     * @return  订单
     */
    @Select("SELECT o.* FROM t_user u,t_order o,t_orderInfo oi WHERE u.realName=o.customerName AND o.pk_order = oi.fk_order\n" +
            "AND u.userType = #{userType} \n" +
            "AND o.date = #{date}\n" +
            "AND oi.type = #{productType}")
    List<OrderBean> findByItem(@Param("productType") String productType,@Param("date")  LocalDate date,@Param("userType")  String userType);

    /**
     * 查询“普通用户”消费情况
     * @return 用户集合
     */
    /**
     * 查询“普通用户”消费情况
     * @return 用户集合
     */
    @Select(("SELECT u.userName, u.realName, u.phone, u.sex, u.`status`, IFNULL(SUM(o.totalPrice), 0) as price\n" +
            "FROM t_user u\n" +
            "LEFT JOIN t_order o ON u.realName=o.customerName\n" +
            "WHERE u.userType = '普通用户'\n" +
            "GROUP BY u.userName, u.realName, u.phone, u.sex, u.`status`\n" +
            "ORDER BY price DESC\n"))
    IPage<UserBean> findByUserPrice(Page pageNO);
    public IPage<OrderBean> cutOrderByItemS(Page pg, @Param("type") String type, @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

}
